package ebj.common.annotation.demo_data_provider_manager.data_provider;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import com.alibaba.fastjson.JSONObject;

import ebj.common.annotation.demo_data_provider_manager.anno.ProviderAttribute;
import ebj.common.annotation.demo_data_provider_manager.anno.ProviderName;
import ebj.common.annotation.demo_data_provider_manager.anno.ProviderAttribute.AttributeType;
import ebj.common.annotation.demo_data_provider_manager.anno.QueryParameter;
import ebj.common.annotation.demo_data_provider_manager.anno.QueryParameter.Type;

@ProviderName("jdbc")
public class JDBCDataProvider extends DataProvider {
    // datasource type, eg. Mysql, PostgreSQL, Presto, Kylin and so on.
    @ProviderAttribute(name="数据源", type=AttributeType.Input, defaultValue="eg. 'Mysql' or 'mysql' for MySQL", order=1)
    private String DATASOURCE = "datasource";
    @ProviderAttribute(name="驱动", type=AttributeType.Input, defaultValue="eg. 'com.mysql.jdbc.Driver' for MySQL", order=2)
    private String DRIVER = "driver";
    @ProviderAttribute(name="主机", type=AttributeType.Input, defaultValue="IP or Domain or Hostname", order=3)
    private String HOST = "host";
    @ProviderAttribute(name="端口", type=AttributeType.Input, defaultValue="eg. '3306' for MySQL", order=4)
    private String PORT = "port";
    @ProviderAttribute(name="数据库", type=AttributeType.Input, defaultValue="eg. 'test'", order=5)
    private String DATABASE = "database";
    @ProviderAttribute(name="用户", type=AttributeType.Input, defaultValue="user name for login DB", order=6)
    private String USER = "user";
    @ProviderAttribute(name="密码", type=AttributeType.Password, order=7)
    private String PASSWORD = "password";

    @QueryParameter(name="SQL语句", type=Type.TextArea, order=1)
    private String SQL_STR = "sql";

    @Override
    public String[][] getData(Map<String, String> datasourceConfig,
            Map<String, String> query) throws Exception {
        String driver = datasourceConfig.get(DRIVER);
        String datasource = datasourceConfig.get(DATASOURCE);
        String host = datasourceConfig.get(HOST);
        String port = datasourceConfig.get(PORT);
        String database = datasourceConfig.get(DATABASE);
        String user = datasourceConfig.get(USER);
        String password = datasourceConfig.get(PASSWORD);
        
        System.out.println(JSONObject.toJSONString(query));
        String sql = query.get(SQL_STR);
        
        /**
         * jdbc:oracle:thin:username/password@x.x.x.1:1521:SID
         * jdbc:mysql://10.2.26.59:3306/adhoc
         * jdbc:postgresql://127.0.0.1:5432/test
         * jdbc:presto://master.host:port
         * jdbc:presto://master.host:port/catalog
         * jdbc:presto://master.host:port/catalog/schema
         * jdbc:kylin://localhost:7070/kylin_project_name
         * jdbc:elasticsearch://localhost:9200/es_index_name(结合es-sql, 仿照Presto JDBC, Kylin JDBC等自己实现)
         */
        String jdbc_conn_url_tmpl = "jdbc:%s://%s:%s/%s"
                + "?autoReconnect=true"
                + "&failOverReadOnly=false"
                + "&useUnicode=true"
                + "&characterEncoding=utf-8"
                + "&rewriteBatchedStatements=true"
                + "&allowMultiQueries=true";
        String jdbc_conn_url = String.format(jdbc_conn_url_tmpl
                , datasource
                , host
                , port
                , database);
        

        Class.forName(driver);
        Properties props = new Properties();
        props.setProperty("user", user);
        props.setProperty("password", password);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<String[]> list = null;

        try {
//            System.err.println(jdbc_conn_url);
            con = DriverManager.getConnection(jdbc_conn_url, props);

            ps = con.prepareStatement(sql);

            rs = ps.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            list = new LinkedList<>();
            String[] row = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                row[i] = metaData.getColumnName(i + 1);
            }
            list.add(row);
            while (rs.next()) {
                row = new String[columnCount];
                for (int j = 0; j < columnCount; j++) {
                    row[j] = rs.getString(j + 1);
                }
                list.add(row);
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                }
            }
        }
        return list.toArray(new String[][]{});
    }
    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return this.getClass().getSimpleName() + "[" 
//                + this.host + ", "
//                + this.user + ", "
//                + this.pass 
                + "]";
    }
}
